//****************************************************************
//* Copyright (c) 2004 Ford Motor Company. All Rights Reserved.
//*
//* $$Workfile:   VolumeDao.java  $$
//* $$Revision:   1.0  $$
//* $$Author:   TMALDON4  $$
//* $$Date:   Mar 30 2009 13:57:52  $$
//*
//*****************************************************************
package ar.com.ford.it.fullEconomics.persistence.dao;

import java.io.FileNotFoundException;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.Timestamp;
import java.util.Collection;
import oracle.toplink.expressions.Expression;
import oracle.toplink.expressions.ExpressionBuilder;
import oracle.toplink.queryframework.ReadAllQuery;
import oracle.toplink.queryframework.ReportQuery;
import oracle.toplink.queryframework.ValueReadQuery;
import oracle.toplink.sessions.Session;
import oracle.toplink.tools.sessionmanagement.SessionManager;
import ar.com.ford.it.fullEconomics.exception.FullEconomicsException;
import ar.com.ford.it.fullEconomics.persistence.dto.Ncas012VehicleVw;
import ar.com.ford.it.fullEconomics.persistence.dto.Ncas101CatalogVw;
import ar.com.ford.it.fullEconomics.persistence.dto.Teesg01Plant;
import ar.com.ford.it.fullEconomics.persistence.dto.Teesp11Volume;
import ar.com.ford.it.webengine.persistence.PersistenceUtil;
import com.ford.it.logging.ILogger;
import com.ford.it.logging.LogFactory;
import com.ford.it.persistence.PersistenceException;
import com.ford.it.persistence.PersistenceManager;
import com.ford.it.persistence.Query;
import com.ford.it.persistence.Transaction;

public class VolumeDao extends Dao {

	private static final String CLASS_NAME = VolumeDao.class.getName();

	private static final ILogger log = LogFactory.getInstance().getLogger(
			CLASS_NAME);

	/**
	 * Busca los volumenes para el proceso de mix, dado un vehiculo seleccionado
	 * 
	 * @return
	 * @throws PersistenceException
	 */
	public Collection<Teesp11Volume> findVolumesForMix(
			Ncas012VehicleVw vehicleLine, Teesg01Plant teesg01Plant,
			Timestamp fecha, Timestamp fechaFin, Timestamp fechaInicioMes)
			throws PersistenceException {

		String METHOD_NAME = "findVolumesForMix";
		log.entering(CLASS_NAME, METHOD_NAME);

		Collection<Teesp11Volume> resultCol = null;
		PersistenceManager pm = null;

		pm = PersistenceUtil.getPersistenceManager();

		Query query = pm.newQuery("findVolumesForMix", Teesp11Volume.class);
		Object[] parametros = { vehicleLine, teesg01Plant, fecha,
				fechaFin, fechaInicioMes };
		query.setParameterArray(parametros);

		resultCol = (Collection) pm.executeQuery(query);

		log.exiting(CLASS_NAME, METHOD_NAME);
		return resultCol;
	}
    
    public Collection<Teesp11Volume> findVolumesByLine(Timestamp date, Ncas012VehicleVw line, Teesg01Plant plant)
            throws PersistenceException {

        String METHOD_NAME = "findVolumesByLine";
        log.entering(CLASS_NAME, METHOD_NAME);

        Collection<Teesp11Volume> resultCol = null;
        Session session = null;

        try {
            session = SessionManager.getManager().getSession(
                    this.getSessionName());
        } catch (FileNotFoundException e) {
            throw new PersistenceException(e);
        }
        ExpressionBuilder builder = new ExpressionBuilder();
        ReadAllQuery query = new ReadAllQuery(Teesp11Volume.class);

        Expression exp = null;

        // comparo el agreement
        exp = builder.get("eesp11PlantCodeC").equal(plant);
        exp = exp.and(builder.get("ncas101PlntcatVw").get("ncas101CatalogVw").get("cas101VehicleCodeC").equal(line));
        exp = exp.and(builder.get("eesp11VolumEffectiveDateY").equal(date));

        query.setSelectionCriteria(exp);

        resultCol = (Collection<Teesp11Volume>) session.executeQuery(query);

        log.exiting(CLASS_NAME, METHOD_NAME);
        return resultCol;
    }

	/**
	 * Obtiene el volumen por catalogo fecha y planta
	 * 
	 * @param
	 * @return
	 * @throws FullEconomicsException
	 */
	public Collection<Teesp11Volume> findVolumeByCatalogDatePlant(
			Ncas101CatalogVw catalog, Timestamp fecha,
			Teesg01Plant teesg01Plant) throws PersistenceException {

		String METHOD_NAME = "findVolumeByCatalogDatePlant";
		log.entering(CLASS_NAME, METHOD_NAME);

		Collection<Teesp11Volume> resultCol = null;

		Session session = null;

		try {
			session = SessionManager.getManager().getSession(
					this.getSessionName());
		} catch (FileNotFoundException e) {
			throw new PersistenceException(e);
		}
		ExpressionBuilder builder = new ExpressionBuilder();
		ReadAllQuery query = new ReadAllQuery(Teesp11Volume.class);

		Expression exp = null;

		// comparo la vigencia del volumen
		exp = builder.get("eesp11VolumEffectiveDateY").equal(fecha);

		// comparo la planta
		exp = exp.and(builder.get("eesp11PlantCodeC").equal(teesg01Plant));

		// comparo el catalogo
		exp = exp.and(builder.get("ncas101PlntcatVw").get("ncas101CatalogVw")
				.equal(catalog));

		query.setSelectionCriteria(exp);

		resultCol = (Collection<Teesp11Volume>) session.executeQuery(query);

		log.exiting(CLASS_NAME, METHOD_NAME);
		return resultCol;
	}

	/**
	 * Da de alta el Volume pasado por parametro
	 * 
	 * @param voolume
	 */
	public void createVolume(Teesp11Volume volume)
			throws PersistenceException {

		String METHOD_NAME = "createVolume";
		log.entering(CLASS_NAME, METHOD_NAME);

		Transaction tx = null;

		tx = PersistenceUtil.getTransaction();
		tx.registerInsert(volume);

		log.exiting(CLASS_NAME, METHOD_NAME);
	}

	/**
	 * Obtiene el volumen total para un rango de fechas y una linea de vehiculo
	 * 
	 * @param
	 * @return
	 * @throws FullEconomicsException
	 */
	public BigInteger getVolumeByVehicleLineTest(Timestamp fechaInicio,
			Timestamp fechaFin, Teesg01Plant teesg01Plant,
			Ncas012VehicleVw vehicleLine) throws PersistenceException {

		String METHOD_NAME = "getVolumeByVehicleLine";
		log.entering(CLASS_NAME, METHOD_NAME);

		BigInteger result = null;

		Session session = null;

		try {
			session = SessionManager.getManager().getSession(
					this.getSessionName());
		} catch (FileNotFoundException e) {
			throw new PersistenceException(e);
		}
		ExpressionBuilder builder = new ExpressionBuilder();
		Expression exp = null;

		// comparo la vigencia del volumen
		exp = builder.get("eesp11VolumEffectiveDateY").greaterThanEqual(
				fechaInicio);
		exp = exp.and(builder.get("eesp11VolumEffectiveDateY").lessThanEqual(
				fechaFin));

		// comparo la planta
		exp = exp.and(builder.get("eesp11PlantCodeC").equal(teesg01Plant));

		// comparo el vehiculo
		exp = exp.and(builder.get("ncas101PlntcatVw").get("ncas101CatalogVw")
				.get("cas101VehicleCodeC").equal(vehicleLine));

		ReportQuery report = new ReportQuery(Teesp11Volume.class, exp);
		report.addAttribute("eesp11VolumActualQuantityQ");
		report.addAttribute("eesp11VolumFutureQuantityQ");
		report.addAttribute("cas101VehicleCodeC", builder.get(
				"ncas101PlntcatVw").get("ncas101CatalogVw").get(
				"cas101VehicleCodeC"));

		report.addGrouping("cas101VehicleCodeC");

		result = (BigInteger) session.executeQuery(report);

		log.exiting(CLASS_NAME, METHOD_NAME);
		return result;
	}

	/**
	 * Obtiene el volumen total para un rango de fechas y una linea de vehiculo
	 * 
	 * @param
	 * @return
	 * @throws FullEconomicsException
	 */
	public BigDecimal getVolumeByVehicleLine(Timestamp fechaInicio,
			Timestamp fechaFin, Teesg01Plant teesg01Plant,
			Ncas012VehicleVw vehicleLine) throws PersistenceException {

		Session session = null;

		try {
			session = SessionManager.getManager().getSession(
					this.getSessionName());
		} catch (FileNotFoundException e) {
			throw new PersistenceException(e);
		}
		
		
		
		ValueReadQuery query = new ValueReadQuery();
		String sqlString = "SELECT sum(t0.EESP11_VOLUM_ACTUAL_QUANTITY_Q+t0.EESP11_VOLUM_FUTURE_QUANTITY_Q)"
				+ " FROM FULLEE.fullee_ddl.TEESP11_VOLUME t0, FULLEE.fullee_ddl.NCAS101_PLNTCAT_VW t2, FULLEE.fullee_ddl.NCAS101_CATALOG_VW t1"
				+ " WHERE (((((t0.EESP11_VOLUM_EFFECTIVE_DATE_Y >= '"+fechaInicio+"') AND (t0.EESP11_VOLUM_EFFECTIVE_DATE_Y <= '"+fechaFin+"')"
				+ " ) AND (t0.EESP11_PLANT_CODE_C = '"+teesg01Plant.getEesg01PlantCodeC()+"')) AND (t1.CAS101_VEHICLE_CODE_C = '"+vehicleLine.getCas012VehicleCodeC()+"')) AND"
				+ " (((t2.CAS101_PLANT_CODE_C = t0.EESP11_PLANT_CODE_C)"
				+ " AND ((t2.CAS101_CATAL_SEQ_CODE_C = t0.EESP11_CATAL_SEQ_CODE_C)"
				+ " AND ((t2.CAS101_CATAL_TMA_CODE_C = t0.EESP11_CATAL_TMA_CODE_C) "
				+ " AND ((t2.CAS101_VEHICLE_CODE_C = t0.EESP11_VEHICLE_CODE_C) "
				+ " AND (t2.CAS101_CATAL_MODELYEAR_CODE_C = t0.EESP11_CATAL_MODELYEAR_CODE_C)))))"
				+ " AND ((t1.CAS101_CATAL_SEQ_CODE_C = t2.CAS101_CATAL_SEQ_CODE_C) "
				+ " AND ((t1.CAS101_CATAL_TMA_CODE_C = t2.CAS101_CATAL_TMA_CODE_C)"
				+ " AND ((t1.CAS101_CATAL_MODELYEAR_CODE_C = t2.CAS101_CATAL_MODELYEAR_CODE_C)"
				+ " AND (t1.CAS101_VEHICLE_CODE_C = t2.CAS101_VEHICLE_CODE_C))))))"
				+ " group by t1.CAS101_VEHICLE_CODE_C";

		query.setSQLString(sqlString);

		Object obj = session.executeQuery(query);
		if (obj == null)
			return new BigDecimal(0);
		return new BigDecimal(obj.toString());
	}

}
